Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

'********************************************
'Object to perform all database functions
'********************************************

Friend Class DataBaseUtility

    Private lRetCode As Integer
    Private lErrCode As Long
    Private sErrMsg As String
    Private SQL As String
    Private EH As New ErrorHandler
    Private TH As TextHandler

    Private Con As SqlConnection
    Private CMD As SqlCommand
    Private DA As SqlDataAdapter

    'instantiate necessary objects on object creation
    Sub New()
        Con = New SqlConnection
        CMD = New SqlCommand
        DA = New SqlDataAdapter
    End Sub

    'Retrieve packaging data used for printing the finishing document
    Friend Function GetPackageData(ByRef AL As ArrayList) As DataTable

        Dim DT As New DataTable
        Dim SQL As String

        DA = New SqlDataAdapter
        CMD = New SqlCommand
        TH = New TextHandler

        Dim TabDelKeyValues As String = TH.AssembleTabDelString(AL.Item(0).ToString, AL.Item(1).ToString, AL.Item(2).ToString)

        SQL = "SELECT ID As 'ID', RollNum As 'Roll No.', Insp As 'Inspection', CACD As 'CA CD', Yards As 'Yards', Eigths As 'Eigths' FROM EQP_BATCHPACK WHERE TabDelKeyValues = '" & TabDelKeyValues & "'"
        CMD.CommandText = SQL
        CMD.Connection = Con
        DA.SelectCommand = CMD

        Connect()
        DA.Fill(DT)
        Disconnect()

        Return DT

    End Function

    'Retrieve general data for printing the finishing and barcode label documents
    Friend Function GetDataForDocuments(ByVal ItemCode As String, ByVal BatchNum As String, ByVal WH As String, ByVal LotNo As String) As DataSet

        Dim DS As New DataSet
        Dim SQL As New StringBuilder
        Dim TH As New TextHandler

        Dim TabDelKeyValues As String = TH.AssembleTabDelString(ItemCode, BatchNum, WH)

        SQL.Append("SELECT * FROM OIBT WHERE (BatchNum = '" & BatchNum & "' AND WhsCode = '" & WH & "' AND ItemCode = '" & ItemCode & "')")
        SQL.Append("SELECT U_PutupDes, U_Markings FROM OWOR WHERE DocNum = '" & LotNo & "' ")
        SQL.Append("SELECT * FROM EQP_BATCHPACK WHERE TabDelKeyValues = '" & TabDelKeyValues & "'")

        CMD = New SqlCommand
        DA = New SqlDataAdapter

        CMD.CommandText = SQL.ToString
        CMD.Connection = Con
        DA.SelectCommand = CMD

        Connect()
        DA.Fill(DS)
        Disconnect()

        Return DS

    End Function

    'If a batch is successfully printed, mark it as such
    Friend Sub UpdatePrintedRecord(ByVal ItemCode As String, ByVal BatchNum As String, ByVal WH As String)

        Dim SQL As String
        SQL = "UPDATE EQP_BatchLog SET PrintStatus = 'T' WHERE (ItemCode = '" & ItemCode & "' And Batchnum = '" & BatchNum & "' And WhsCode = '" & WH & "')"

        CMD = New SqlCommand
        CMD.CommandText = SQL
        CMD.Connection = Con

        Connect()
        CMD.ExecuteNonQuery()
        Disconnect()

    End Sub

    'New batches are updated from the SBO_Transaction_Notification stored procedure to the EQP_BatchLog table.
    'Each update contains only the three primary key values from the newly created batch in a tab delimited STRING
    'This function breaks apart the tabbed string, queries the database for relevent data, and updates the EQP_BatchLog
    'table.
    Friend Sub SynchWithOIBT(ByVal AL As ArrayList)

        Dim i As Integer
        Dim DT As New DataTable
        Dim SplitVals As ArrayList
        TH = New TextHandler

        Connect()

        For i = 0 To (AL.Count - 1)
            SplitVals = New ArrayList
            CMD = New SqlCommand
            SplitVals = TH.BreakApartTabDelStr(AL.Item(i))
            SQL = TH.BuildUpdateSQL(SplitVals, AL.Item(i))
            CMD.CommandText = SQL
            CMD.Connection = Con
            CMD.ExecuteNonQuery()
        Next

        Disconnect()

    End Sub

    'Gets an arraylist with all the unsynchronized batches
    Friend Function GetTabDelimitedStrings() As ArrayList

        Dim AL As New ArrayList, RDR As SqlDataReader
        Dim SQL As String = "SELECT T0.TabDelKeyValues FROM EQP_BATCHLOG T0 WHERE (T0.BatchNum IS NULL AND T0.ItemCode IS NULL AND T0.WhsCode IS NULL)"
        CMD.CommandText = SQL
        Connect()
        RDR = CMD.ExecuteReader

        While RDR.Read
            AL.Add(RDR("TabDelKeyValues").ToString)
        End While

        RDR.Close()
        Disconnect()
        Return AL

    End Function

    'Updates packaging data associated with a batch from the frmPackaging form.
    Friend Sub PackagingRecordUpdate(ByVal ID As String, ByVal RollNum As String, ByVal Insp As String, ByVal CACD As String, ByVal Yards As String, ByVal Eigths As String)

        Dim Upd As String = "UPDATE EQP_BATCHPACK SET RollNum = '" & RollNum & "', Insp = '" & Insp & "', CACD = '" & CACD & "', Yards = '" & Yards & "', Eigths = '" & Eigths & "' WHERE ID = '" & ID & "'"

        CMD = New SqlCommand

        CMD.Connection = Con
        CMD.CommandText = Upd

        Connect()
        CMD.ExecuteNonQuery()
        Disconnect()

    End Sub

    'Adds new packaging information to an existing batch in EQP_BatchLog
    Friend Sub PackagingRecordAdd(ByVal TabDelKeyValues As String, ByVal RollNum As String, ByVal Insp As String, ByVal CACD As String, ByVal Yards As String, ByVal Eigths As String)

        Dim INS As String = "INSERT INTO EQP_BATCHPACK (TabDelKeyValues, RollNum, Insp, CACD, Yards, Eigths) VALUES ('" & TabDelKeyValues & "', '" & RollNum & "', '" & Insp & "', '" & CACD & "', '" & Yards & "', '" & Eigths & "')"

        CMD = New SqlCommand

        CMD.Connection = Con
        CMD.CommandText = INS

        Connect()
        CMD.ExecuteNonQuery()
        Disconnect()

    End Sub

    'Deletes a packaging record from the frmPackaging form.
    Friend Sub PackingRecordDelete(ByVal ID As String)

        Dim SQL As String
        SQL = "DELETE FROM EQP_BATCHPACK WHERE ID = '" & ID & "'"

        CMD = New SqlCommand
        CMD.CommandText = SQL
        CMD.Connection = Con

        Connect()
        CMD.ExecuteNonQuery()
        Disconnect()

    End Sub

    'Check if there is packaging info on a specific batch
    Friend Function PackingRecordExists(ByVal ID As String) As Boolean

        Dim SQL As String = "SELECT * FROM EQP_BATCHPACK WHERE ID = '" & ID & "'"
        Dim RDR As SqlDataReader

        CMD = New SqlCommand
        CMD.Connection = Con

        CMD.CommandText = SQL

        Connect()
        RDR = CMD.ExecuteReader

        Select Case RDR.HasRows
            Case True
                Disconnect()
                Return True
            Case False
                Disconnect()
                Return False
        End Select

    End Function

    'Return a datatable containing batch information in EQP_BatchLog by use of the 3 necessary PKs
    Friend Function GetBatchLogData(ByVal BatchFrom As String, ByVal BatchTo As String, ByVal ItemFrom As String, ByVal ItemTo As String, ByVal LotFrom As String, ByVal LotTo As String, ByVal PrintStatus As String) As DataTable

        Dim DT As New DataTable
        Dim SQL As String
        DA = New SqlDataAdapter
        CMD = New SqlCommand

        SQL = TH.BuildSelectSQL(BatchFrom, BatchTo, ItemFrom, ItemTo, LotFrom, LotTo, PrintStatus)
        CMD.CommandText = SQL
        CMD.Connection = Con
        DA.SelectCommand = CMD

        Connect()
        DA.Fill(DT)
        Disconnect()

        Return DT

    End Function

    'Creates a SQL table based on a string of SQL
    Friend Sub CreateTable(ByVal SQL As String)

        CMD.CommandText = SQL
        CMD.Connection = Con
        Connect()
        CMD.ExecuteReader()
        Disconnect()

    End Sub

    'Function to return a single value; usually for max or min sql queries
    Friend Function PerformScalarQuery(ByVal SQL1 As String) As String

        Dim RDR As SqlDataReader, Val As String = "0"
        CMD.CommandText = SQL1
        Connect()
        RDR = CMD.ExecuteReader
        If RDR.HasRows = True Then
            While RDR.Read
                Val = RDR.Item(0).ToString
            End While
        End If

        RDR.Close()

        Return Val

    End Function

    Friend Function DeleteGreigeGoodsFromLog() As Boolean

        CMD.CommandText = "DELETE FROM EQP_BATCHLOG WHERE TabDelKeyValues LIKE 'G%'"
        Connect()
        CMD.ExecuteNonQuery()
        Disconnect()

        Return True

    End Function

    Private Sub Connect()

        If Con.State <> ConnectionState.Open Then

            Dim TFH As New TextHandler
            Dim AL As ArrayList = TFH.ReadFileLinesToArrayList("Settings.ini")
            Dim DataSource As String, InitialCat As String, User As String, Pass As String

            Try
                DataSource = AL.Item(0)
            Catch ex As Exception
                DataSource = ""
            End Try

            Try
                InitialCat = AL.Item(1)
            Catch ex As Exception
                InitialCat = ""
            End Try

            Try
                User = AL.Item(2)
            Catch ex As Exception
                User = ""
            End Try

            Try
                Pass = AL.Item(3)
            Catch ex As Exception
                Pass = ""
            End Try

            Dim STR As String = "Data Source=" & DataSource & ";Initial Catalog=" & InitialCat & ";Persist Security Info=True;User ID=" & User & ";Password=" & Pass
            'Dim STR As String = "Data Source=GREGMOBILE\TWOTHOUSANDFIVE;Initial Catalog=CSPDEV;Persist Security Info=True;User ID=sa;Password=wa4hkx"
            Con.ConnectionString = STR
            Con.Open()

        End If

    End Sub

    Friend Sub Disconnect()
        If Con.State <> ConnectionState.Closed Then
            Con.Close()
        End If
    End Sub

End Class
